# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


import pymysql
# useful for handling different item types with a single interface
from itemadapter import ItemAdapter

from scrapy_base.items import EnterpirseItem, ScrapyBaseItem


class ScrapyBasePipeline:
    def __init__(self, host, port, user, password, dbname):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.dbname = dbname

    @classmethod
    def from_crawler(cls, crawler):
        return cls(
            host=crawler.settings.get('MYSQL_HOST'),
            port=crawler.settings.get('MYSQL_PORT'),
            user=crawler.settings.get('MYSQL_USER'),
            password=crawler.settings.get('MYSQL_PASSWORD'),
            dbname=crawler.settings.get('MYSQL_DBNAME')
        )

    def open_spider(self, spider):
        self.conn = pymysql.connect(host=self.host, port=self.port, user=self.user,
                                    password=self.password, db=self.dbname, charset='utf8')
        self.cursor = self.conn.cursor()

    def close_spider(self, spider):
        self.cursor.close()
        self.conn.close()

    def process_item(self, item, spider):

        try:
            if isinstance(item, ScrapyBaseItem):
                sql = "INSERT INTO user (name) VALUES (%s)"
                self.cursor.execute(sql, (item['name']))
            elif isinstance(item, EnterpirseItem):
                sql = "INSERT INTO spider.user_enterprise(name, legal_representative, establishment_date, registered_capital, operating_status, area_code, province, area, address, unified_social_credit_code, contact_phone) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                self.cursor.execute(sql, (item['name'], item['legal_representative'], item['establishment_date'], item['registered_capital'],
                                    item['operating_status'], item['area_code'], item['province'], item['area'], item['address'], item['unified_social_credit_code'], item['contact_phone']))

            self.conn.commit()
            print("存储：{}", item)
        except Exception as e:
            self.conn.rollback()
            raise e

        return item
